# app/crud.py

from sqlalchemy.orm import Session
from . import models, schemas
from datetime import datetime
import re

# DCO 正则表达式
DCO_REGEX = re.compile(r'^Signed-off-by:\s+([^<]+)<([^>]+)>$', re.IGNORECASE)

def create_webhook_event(db: Session, event: schemas.WebhookEventSchema):
    db_event = models.WebhookEvent(
        repository_name=event.repository_name,
        repository_owner=event.repository_owner,
        pusher=event.pusher,
        ref=event.ref,
        before_sha=event.before_sha,
        after_sha=event.after_sha,
        received_at=datetime.utcnow()
    )
    db.add(db_event)
    db.commit()
    db.refresh(db_event)
    return db_event

def create_invalid_commit(db: Session, commit: schemas.InvalidCommitSchema, event_id: int):
    db_commit = models.InvalidCommit(
        sha=commit.sha,
        author=commit.author,
        message=commit.message,
        url=commit.url,
        detected_at=datetime.utcnow(),
        event_id=event_id
    )
    db.add(db_commit)
    db.commit()
    db.refresh(db_commit)
    return db_commit

def get_events(db: Session, filters: dict):
    query = db.query(models.WebhookEvent)

    if filters.get('repository'):
        query = query.filter(models.WebhookEvent.repository_name.ilike(f"%{filters['repository']}%"))
    if filters.get('owner'):
        query = query.filter(models.WebhookEvent.repository_owner.ilike(f"%{filters['owner']}%"))
    if filters.get('pusher'):
        query = query.filter(models.WebhookEvent.pusher.ilike(f"%{filters['pusher']}%"))
    if filters.get('date_from'):
        query = query.filter(models.WebhookEvent.received_at >= filters['date_from'])
    if filters.get('date_to'):
        query = query.filter(models.WebhookEvent.received_at <= filters['date_to'])
    if 'invalid_commits' in filters:
        if filters['invalid_commits'] == 'yes':
            query = query.join(models.WebhookEvent.invalid_commits).filter(models.InvalidCommit.id.isnot(None)).distinct()
        elif filters['invalid_commits'] == 'no':
            query = query.outerjoin(models.WebhookEvent.invalid_commits).filter(models.InvalidCommit.id.is_(None)).distinct()

    return query.order_by(models.WebhookEvent.received_at.desc()).all()
